{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Help Desk - Easy\n",
    "\n",
    "## Scenario\n",
    "A software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made.\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/3/38/Helpdesk.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "There are three issues that include the words \"index\" and \"Oracle\". Find the call_date for each of them\n",
    "\n",
    "```\n",
    "+---------------------+----------+\n",
    "| call_date           | call_ref |\n",
    "+---------------------+----------+\n",
    "| 2017-08-12 16:00:00 |     1308 |\n",
    "| 2017-08-16 14:54:00 |     1697 |\n",
    "| 2017-08-16 19:12:00 |     1731 |\n",
    "+---------------------+----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>call_date</th>\n",
       "        <th>call_ref</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12 16:00:00</td>\n",
       "        <td>1308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16 14:54:00</td>\n",
       "        <td>1697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16 19:12:00</td>\n",
       "        <td>1731</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2017, 8, 12, 16, 0), 1308),\n",
       " (datetime.datetime(2017, 8, 16, 14, 54), 1697),\n",
       " (datetime.datetime(2017, 8, 16, 19, 12), 1731)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Call_date\" call_date, \"Call_ref\" call_ref\n",
    "  FROM \"Issue\"\n",
    "    WHERE \"Detail\" LIKE '%index%' AND \"Detail\" LIKE '%Oracle%';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Samantha Hall made three calls on 2017-08-14. Show the date and time for each\n",
    "\n",
    "```\n",
    "+---------------------+------------+-----------+\n",
    "| call_date           | first_name | last_name |\n",
    "+---------------------+------------+-----------+\n",
    "| 2017-08-14 10:10:00 | Samantha   | Hall      |\n",
    "| 2017-08-14 10:49:00 | Samantha   | Hall      |\n",
    "| 2017-08-14 18:18:00 | Samantha   | Hall      |\n",
    "+---------------------+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Call_date</th>\n",
       "        <th>First_name</th>\n",
       "        <th>Last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14 10:10:00</td>\n",
       "        <td>Samantha</td>\n",
       "        <td>Hall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14 10:49:00</td>\n",
       "        <td>Samantha</td>\n",
       "        <td>Hall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14 18:18:00</td>\n",
       "        <td>Samantha</td>\n",
       "        <td>Hall</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2017, 8, 14, 10, 10), 'Samantha', 'Hall'),\n",
       " (datetime.datetime(2017, 8, 14, 10, 49), 'Samantha', 'Hall'),\n",
       " (datetime.datetime(2017, 8, 14, 18, 18), 'Samantha', 'Hall')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Call_date\", \"First_name\", \"Last_name\"\n",
    "FROM \"Issue\" JOIN \"Caller\" ON (\"Issue\".\"Caller_id\"=\"Caller\".\"Caller_id\")\n",
    "WHERE \"First_name\"='Samantha' AND \"Last_name\"='Hall' AND Date(\"Call_date\")='2017-08-14';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "There are 500 calls in the system (roughly). Write a query that shows the number that have each status.\n",
    "\n",
    "```\n",
    "+--------+--------+\n",
    "| status | Volume |\n",
    "+--------+--------+\n",
    "| Closed |    486 |\n",
    "| Open   |     10 |\n",
    "+--------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>status</th>\n",
       "        <th>Volume</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Closed</td>\n",
       "        <td>486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Open</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Closed', 486), ('Open', 10)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Status\" status, COUNT(*) \"Volume\" FROM \"Issue\"\n",
    "GROUP BY \"Status\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Calls are not normally assigned to a manager but it does happen. How many calls have been assigned to staff who are at Manager Level?\n",
    "\n",
    "```\n",
    "+------+\n",
    "| mlcc |\n",
    "+------+\n",
    "|   51 |\n",
    "+------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>mlcc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(51,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(\"Issue\".\"Call_ref\") mlcc\n",
    "FROM \"Issue\" JOIN \"Staff\" ON (\"Issue\".\"Assigned_to\"=\"Staff\".\"Staff_code\") \n",
    "  JOIN \"Level\" ON (\"Staff\".\"Level_code\"=\"Level\".\"Level_code\")\n",
    "WHERE \"Manager\"='Y';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Show the manager for each shift. Your output should include the shift date and type; also the first and last name of the manager.\n",
    "\n",
    "```\n",
    "+------------+------------+------------+-----------+\n",
    "| Shift_date | Shift_type | first_name | last_name |\n",
    "+------------+------------+------------+-----------+\n",
    "| 2017-08-12 | Early      | Logan      | Butler    |\n",
    "| 2017-08-12 | Late       | Ava        | Ellis     |\n",
    "| 2017-08-13 | Early      | Ava        | Ellis     |\n",
    "| 2017-08-13 | Late       | Ava        | Ellis     |\n",
    "| 2017-08-14 | Early      | Logan      | Butler    |\n",
    "| 2017-08-14 | Late       | Logan      | Butler    |\n",
    "| 2017-08-15 | Early      | Logan      | Butler    |\n",
    "| 2017-08-15 | Late       | Logan      | Butler    |\n",
    "| 2017-08-16 | Early      | Logan      | Butler    |\n",
    "| 2017-08-16 | Late       | Logan      | Butler    |\n",
    "+------------+------------+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Shift_date</th>\n",
       "        <th>Shift_type</th>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12</td>\n",
       "        <td>Early</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12</td>\n",
       "        <td>Late</td>\n",
       "        <td>Ava</td>\n",
       "        <td>Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-13</td>\n",
       "        <td>Early</td>\n",
       "        <td>Ava</td>\n",
       "        <td>Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-13</td>\n",
       "        <td>Late</td>\n",
       "        <td>Ava</td>\n",
       "        <td>Ellis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Early</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Late</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-15</td>\n",
       "        <td>Early</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-15</td>\n",
       "        <td>Late</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16</td>\n",
       "        <td>Early</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16</td>\n",
       "        <td>Late</td>\n",
       "        <td>Logan</td>\n",
       "        <td>Butler</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2017, 8, 12), 'Early', 'Logan', 'Butler'),\n",
       " (datetime.date(2017, 8, 12), 'Late', 'Ava', 'Ellis'),\n",
       " (datetime.date(2017, 8, 13), 'Early', 'Ava', 'Ellis'),\n",
       " (datetime.date(2017, 8, 13), 'Late', 'Ava', 'Ellis'),\n",
       " (datetime.date(2017, 8, 14), 'Early', 'Logan', 'Butler'),\n",
       " (datetime.date(2017, 8, 14), 'Late', 'Logan', 'Butler'),\n",
       " (datetime.date(2017, 8, 15), 'Early', 'Logan', 'Butler'),\n",
       " (datetime.date(2017, 8, 15), 'Late', 'Logan', 'Butler'),\n",
       " (datetime.date(2017, 8, 16), 'Early', 'Logan', 'Butler'),\n",
       " (datetime.date(2017, 8, 16), 'Late', 'Logan', 'Butler')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT Date(\"Shift_date\") \"Shift_date\", \"Shift_type\", \n",
    "  \"First_name\" first_name, \"Last_name\" last_name\n",
    "    FROM \"Shift\" JOIN \"Staff\" ON (\"Shift\".\"Manager\"=\"Staff\".\"Staff_code\")\n",
    "    ORDER BY \"Shift_date\", \"Shift_type\";"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
